# -*- coding: utf-8 -*-
# @Time    : 2024/1/16  22:04
# @Author  : zhanghao
# @FileName: sqlite.py
# @Software: PyCharm
"""
    Description: 使用sqlite3作为本地存储方案
"""
import sqlite3
import threading

from sqlite3 import Connection, Cursor

from config.logger import Logger
from api.domain.file_result import FileResult


class Sqlite:
    logger = Logger.get()

    def __init__(self):
        pass

    def initTaskTable(self):
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        if self.tableExist('task'):
            self.logger.info('task表已创建.')
            return

        cursor.execute('''CREATE TABLE IF NOT EXISTS task (
                            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                            file_id     CHAR(32)    NOT NULL,
                            file_type   CHAR(8)     NOT NULL,
                            file_link   CHAR(100)   NOT NULL,
                            task_status INT         NOT NULL,
                            res_send    INT         NOT NULL,
                            file_md5    CHAR(32)    NOT NULL,
                            task_result CHAR(100)   NOT NULL,
                            reason      CHAR(100)   NOT NULL,
                            info        CHAR(100)   NOT NULL
                        );''')
        conn.commit()

        # 判断索引是否存在，不存在则建索引
        cursor.execute("SELECT name FROM sqlite_master WHERE type = 'index'")
        conn.commit()

        index_list = cursor.fetchall()
        index_set = set()
        for index in index_list:
            for single_index in index:
                index_set.add(single_index)
        if 'idx_task_status' not in index_set:
            cursor.execute("CREATE INDEX idx_task_status ON task (task_status)")
        if 'idx_file_id' not in index_set:
            cursor.execute("CREATE INDEX idx_file_id ON task (file_id)")
        if 'idx_res_send' not in index_set:
            cursor.execute("CREATE INDEX idx_res_send ON task (res_send)")

        conn.commit()
        conn.close()

        self.logger.info('数据库task表创建成功，索引构建成功')

    def tableExist(self, table_name):
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        cursor.execute("SELECT count(*) from sqlite_master WHERE type = 'table' AND name='%s'" % table_name)
        res = cursor.fetchall()
        conn.commit()

        conn.close()
        self.logger.info(res)
        if res and res[0] == 1:
            return True
        else:
            return False

    def batchInsertTask(self, batch_data: list) -> list:
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        sql = "INSERT INTO task (file_id, file_type, file_link, task_status, res_send, file_md5, task_result, reason, " \
              "info) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        cursor.executemany(sql, batch_data)
        conn.commit()

        ids = []
        for row in cursor.fetchall():
            ids.append(row[0])
            self.logger.info(f"Generated ID: {row[0]}")

        conn.close()
        return ids

    # 更新任务
    def updateTask(self, file_result: FileResult):
        self.logger.info("updateTask: 1")
        conn = sqlite3.connect(database='task.db')
        self.logger.info("updateTask: 2")
        cursor = conn.cursor()
        self.logger.info("updateTask: 3")
        sql = "UPDATE task SET file_id = ?, file_type = ?, file_link = ?, task_status = ?, res_send = ?, file_md5 = ?," \
              " task_result = ?, reason = ?, info = ? Where id = ?"
        params = (file_result.file_id, file_result.file_type, file_result.file_link, file_result.task_status,
                  file_result.res_send, file_result.file_md5, file_result.task_result, file_result.reason,
                  file_result.info, file_result.id)
        self.logger.info("updateTask: 4" + sql)

        cursor.execute(sql, params)
        self.logger.info("updateTask: 5")
        conn.commit()
        self.logger.info("updateTask: 6")
        conn.close()
        self.logger.info("updateTask: 7")

    def updateRunningTask(self):
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        sql = "UPDATE task SET task_status = 0, file_md5 = '', task_result = '', res_send = 0, " \
              "reason = '', info = '{}' Where task_status = 1"

        cursor.execute(sql)
        conn.commit()
        conn.close()

    def taskQuery(self, file_id) -> list:
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        sql = "SELECT * FROM task WHERE file_id = '%s' ORDER BY id DESC LIMIT 1"
        cursor.execute(sql % file_id)
        conn.commit()

        res = cursor.fetchone()
        self.logger.info(res)

        conn.close()
        return res

    def query10Task(self) -> list:
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        sql = "SELECT * FROM task WHERE task_status = 0 ORDER BY id LIMIT 10"
        cursor.execute(sql)
        conn.commit()

        task_data = cursor.fetchall()

        conn.close()
        return task_data

    def queryCompleteTask(self) -> list:
        conn = sqlite3.connect(database='task.db')
        cursor = conn.cursor()
        sql = "SELECT * FROM task WHERE (task_status = 2 OR task_status = 3) and res_send = 0 ORDER BY id LIMIT 10"
        cursor.execute(sql)
        conn.commit()

        task_data = cursor.fetchall()

        conn.close()
        return task_data
